Nested Aggregation Outer Joins

The Nested Aggregation Outer Joins VLDB property allows you define when outer joins are performed on metrics that are defined with nested aggregation functions. A nested aggregation function is when one aggregation function is included within another aggregation function. For example, Sum(Count(Expression)) uses nested aggregation because the Count aggregation is calculated within the Sum aggregation.

These types of metrics can experience unexpected behavior when attempting to use outer joins. This VLDB property provides the following options to control the outer join behavior for metrics that use nested aggregation:

• Do not perform outer join on nested aggregation (default): Outer joins are not used for metrics that use nested aggregation, even if the metric is defined to use an outer join. This option reflects the behavior of all pre-9.0 MicroStrategy releases.
• Do perform outer join on nested aggregation when all formulas have the same level: If all the inner metrics have the same level, which is lower than the report level, and the formula join type for the outer metric is set to outer, then an outer join is performed on the inner metrics.
• Do perform downward outer join on nested aggregation when all formulas can downward outer join to a common lower level: Regardless of whether the inner metrics have the same level, if more than one inner metric has a level which is the child of the levels of other inner metrics and the formula join type for the outer metric is set to outer, then a downward outer join is performed on the relevant inner metrics. The behavior of the downward outer join follows the Downward Outer Join Option VLDB property (see Nested Aggregation Outer Joins).

Levels at Which You Can Set This

Database instance, report, and template